package com.zl.learning.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.List;

import static org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_JPEG;

public class TestPoi {
    public static void main(String[] args) throws IOException {
        //1.创建Excel对象
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);//默认值是100
        //2.创建Sheet对象
        Sheet sheet = wb.createSheet();
        //3.定义一些可复用的对象
        int rowIndex = 0;//行的索引
        int cellIndex = 1;//单元格的索引
        Row nRow = null;
        Cell nCell = null;
        //4.设置列的宽度（列索引，列宽*256  理解为固定写法）
        sheet.setColumnWidth(1, 26 * 256);
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 29 * 256);
        sheet.setColumnWidth(4, 12 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        //5.创建大标题行   大标题：2019年5月份新增用户表
        nRow = sheet.createRow(rowIndex++);//使用的是0,使用完了+1
        //设置大标题行的高度
        nRow.setHeightInPoints(36);
        //6.创建大标题的单元格
        nCell = nRow.createCell(cellIndex);
        //7.合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 5));
        //8.设置大标题内容
        String bigTitle = LocalDate.now().format(DateTimeFormatter.ISO_DATE) + "月份新增用户表";//inputDate  2015-01  2015年1月份出货表
        nCell.setCellValue(bigTitle);
        final CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        nCell.setCellStyle(cellStyle);
        //9.创建小标题内容
        String[] titles = new String[]{"用户名", "性别", "年龄", "手机号", "邮箱"};
        //10.创建小标题行
        nRow = sheet.createRow(rowIndex++);//使用的是1，使用完了再加1
        //设置小标题行高
        nRow.setHeightInPoints(26.25f);
        //12.创建小标题的单元格
        for (String title : titles) {
            nCell = nRow.createCell(cellIndex++);
            //设置小标题内容
            nCell.setCellValue(title);
        }
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        Drawing patriarch = sheet.createDrawingPatriarch();
        //13.获取要生成的数据（数据库内的用户数据）
        List<User> list = List.of(new User("张三", "男", 11, "18955667788", "zhangsan@126.com"));
        //14.遍历数据
        for (User user : list) {
            for (int i = 0; i < 10; i++) {
                //15.创建数据行
                nRow = sheet.createRow(rowIndex++);
                //16.设置数据行高
                nRow.setHeightInPoints(24);
                //17.重置cellIndex
                cellIndex = -1;
                nCell = nRow.createCell(++cellIndex);
                nCell.setCellValue(i);
                nCell.setCellFormula("string");
                //18.创建数据单元格，设置单元格内容
                //用户名
                nCell = nRow.createCell(++cellIndex);
                nCell.setCellValue(user.getUsername());
                //性别
                nCell = nRow.createCell(++cellIndex);
                nCell.setCellValue(user.getSex());
                //年龄
                nCell = nRow.createCell(++cellIndex);
                nCell.setCellValue(user.getAge());
                //手机号
                nCell = nRow.createCell(++cellIndex);
                nCell.setCellValue(user.getPhone());
                //邮箱
                nCell = nRow.createCell(++cellIndex);
                nCell.setCellValue(user.getEmail());
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255, ++cellIndex, rowIndex-1, cellIndex + 1, rowIndex);
                anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
//                patriarch.createPicture(anchor, wb.addPicture(Files.readAllBytes(Paths.get("D:\\2.jpg")), PICTURE_TYPE_JPEG));
                patriarch.createPicture(anchor, wb.addPicture(getImageBytes(new URL("https://gimg2.baidu.com/image_search/src=http%3A%2F%2Fwww.08lr.cn%2Fuploads%2Fallimg%2F170513%2F1-1F513164126.jpg&refer=http%3A%2F%2Fwww.08lr.cn&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=jpeg?sec=1615085204&t=907355d54ae8924d3f5d466574902313")), PICTURE_TYPE_JPEG));
            }
        }
        try (final OutputStream stream = Files.newOutputStream(Paths.get("D:\\1.xlsx"));) {
            wb.write(stream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static byte[] getImageBytes(URL url) {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try (InputStream in = url.openStream()) {
            byte[] bytes = new byte[1024];
            int len = -1;
            while ((len = in.read(bytes)) != -1) {
                out.write(bytes, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return out.toByteArray();
    }
}
